
**************************************************************
* Creates a dataset with 2018 election participation
**************************************************************

* Retrieve table "valdelt_2018" from SQL database
clear all
odbc load, table("valdelt_2018") connectionstring("DRIVER={SQL Server};SERVER={mq02\b};DATABASE={P0846};Trusted_Connection={Yes}")

* Save raw data so I do not need to use odbc each time (which is slow)
save Data/ODBC_valdelt_2018, replace
use Data/ODBC_valdelt_2018, clear
 
* Swedish citizens only (rostratt == "3" are foregin born)
keep if  rostratt == "1" | rostratt == "2"
destring  Rrost, replace
drop if Rrost == .
tab Rrost 

* Keep only relevant variables
keep P0846_LopNr_PersonNr Rrost llkkdddd

* Some individuals occur multiple times in data
* Keep only one case 
* After this step, all personid are unique because all duplicates either voted
* or did not vote (in contrast to the 2010 election data)
duplicates drop

* Use better variable names
rename P0846_LopNr_PersonNr PersonId
rename Rrost Voted2018
rename llkkdddd elec_district

destring elec_district, replace
compress
save "Data/data_voting_2018", replace
